using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;

namespace CQRS.Reports.SqlQueryBuilder
{
    public class MsSqlReportingRepository : IReportingRepository
    {
        private readonly string _connectionString;
        private readonly ISqlDeleteStatementBuilder _sqlDeleteBuilder;
        private readonly ISqlInsertStatementBuilder _sqlInsertBuilder;
        private readonly ISqlSelectStatementBuilder _sqlSelectBuilder;
        private readonly ISqlUpdateStatementBuilder _sqlUpdateBuilder;

        public MsSqlReportingRepository(ISqlSelectStatementBuilder sqlSelectBuilder,
                                        ISqlInsertStatementBuilder sqlInsertBuilder,
                                        ISqlUpdateStatementBuilder sqlUpdateBuilder,
                                        ISqlDeleteStatementBuilder sqlDeleteBuilder)
        {
            _sqlSelectBuilder = sqlSelectBuilder;
            _sqlInsertBuilder = sqlInsertBuilder;
            _sqlUpdateBuilder = sqlUpdateBuilder;
            _sqlDeleteBuilder = sqlDeleteBuilder;
            var stringBuilder = new SqlConnectionStringBuilder();
            stringBuilder.DataSource = @".\SQLEXPRESS";
            stringBuilder.InitialCatalog = "DemoCQRSApplication";
            stringBuilder.ConnectTimeout = 10;
            stringBuilder.IntegratedSecurity = true;
            _connectionString = stringBuilder.ConnectionString;
        }

        #region IReportingRepository Members

        public IEnumerable<TDto> GetByCriteria<TDto>(QueryCriteria queryCriteria) where TDto : Report
        {
            List<TDto> dtos;
            Type dtoType = typeof (TDto);

            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                sqlConnection.Open();

                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        dtos = GetByCriteria<TDto>(sqlTransaction, dtoType, queryCriteria);
                        sqlTransaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
            return dtos;
        }

        public void Save<TDto>(TDto dto) where TDto : Report
        {
            string commandText = _sqlInsertBuilder.CreateSqlInsertStatementFromDto<TDto>();
            QueryCriteria insertCriteria = DtoToCriteria(dto);
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                sqlConnection.Open();

                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        using (var sqlCommand = new SqlCommand(commandText, sqlTransaction.Connection, sqlTransaction))
                        {
                            insertCriteria.AddParameters(sqlCommand);
                            sqlCommand.ExecuteNonQuery();
                        }
                        sqlTransaction.Commit();
                    }
                    catch (Exception)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Update<TDto>(QueryCriteria updateCriteria, QueryCriteria whereCriteria) where TDto : Report
        {
            string commandText = _sqlUpdateBuilder.GetUpdateString<TDto>(updateCriteria, whereCriteria);

            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                sqlConnection.Open();

                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        using (var sqlCommand = new SqlCommand(commandText, sqlTransaction.Connection, sqlTransaction))
                        {
                            updateCriteria.AddParameters(sqlCommand, "update_");
                            whereCriteria.AddParameters(sqlCommand);
                            sqlCommand.ExecuteNonQuery();
                        }
                        sqlTransaction.Commit();
                    }
                    catch (Exception)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Delete<TDto>(QueryCriteria deleteCriteria) where TDto : Report
        {
            string commandText = _sqlDeleteBuilder.CreateSqlDeleteStatementFromDto<TDto>(deleteCriteria);

            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                sqlConnection.Open();

                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        using (var sqlCommand = new SqlCommand(commandText, sqlTransaction.Connection, sqlTransaction))
                        {
                            deleteCriteria.AddParameters(sqlCommand);
                            sqlCommand.ExecuteNonQuery();
                        }
                        sqlTransaction.Commit();
                    }
                    catch (Exception)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }

        #endregion

        private QueryCriteria DtoToCriteria<TDto>(TDto dto) where TDto : Report
        {
            var searchCriteria = new QueryCriteria();

            foreach (PropertyInfo property in dto.GetType().GetProperties().Where(x => ! x.PropertyType.IsGenericType))
                searchCriteria.AddCriteria(property.Name, property.GetValue(dto, new object[] {}));
            return searchCriteria;
        }

        private void HydrateDto<TDto>(SqlTransaction sqlTransaction, TDto dto, Type dtoType, IDataRecord dataReader)
            where TDto : Report
        {
            foreach (PropertyInfo property in dtoType.GetProperties().Where(x => x.PropertyType.IsGenericType))
            {
                object currentPropertyValue = property.GetValue(dto, null);
                property.SetValue(currentPropertyValue, dataReader[property.Name], null);
            }

            foreach (PropertyInfo property in dtoType.GetProperties().Where(x => x.PropertyType.IsGenericType))
            {
                Type childDtoType = property.PropertyType.GetGenericArguments().First();

                object childDtos = GetType()
                    .GetMethod("GetByCriteria", BindingFlags.NonPublic | BindingFlags.Instance)
                    .MakeGenericMethod(childDtoType)
                    .Invoke(this, new object[] {sqlTransaction, childDtoType, CreateSelectObject(dto)});

                property.SetValue(dto, childDtos, new object[] {});
            }
        }

        private static QueryCriteria CreateSelectObject<TDto>(TDto parentDto)
        {
            string columnName = string.Format("{0}Id", parentDto.GetType().Name);
            object columnValue = parentDto.GetType().GetProperty("Id").GetValue(parentDto, new object[] {});

            var searchCriteria = new QueryCriteria();
            searchCriteria.AddCriteria(columnName, columnValue);
            return searchCriteria;
        }

        private List<TDto> GetByCriteria<TDto>(SqlTransaction sqlTransaction,
                                               Type dtoType, QueryCriteria queryCriteria) where TDto : Report
        {
            var dtos = new List<TDto>();
            string commandText = _sqlSelectBuilder.CreateSqlSelectStatementFromDto<TDto>(queryCriteria);

            using (var sqlCommand = new SqlCommand(commandText, sqlTransaction.Connection, sqlTransaction))
            {
                if (queryCriteria != null)
                    queryCriteria.AddParameters(sqlCommand);

                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                {
                    ConstructorInfo dtoConstructor =
                        dtoType.GetConstructors().OrderByDescending(x => x.GetParameters().Count()).FirstOrDefault();

                    while (sqlDataReader.Read())
                    {
                        var dto = ConstructDto<TDto>(dtoType, dtoConstructor, sqlDataReader);
                        HydrateDto(sqlTransaction, dto, dtoType, sqlDataReader);
                        dtos.Add(dto);
                    }
                }
            }
            return dtos;
        }

        private static TDto ConstructDto<TDto>(Type dtoType, ConstructorInfo dtoConstructor, IDataRecord dataReader)
            where TDto : Report
        {
            var constructorArguments = new List<object>();
            
            foreach (ParameterInfo parameter in
                dtoConstructor.GetParameters())
                constructorArguments.Add(dataReader[parameter.Name]);

            return (TDto) dtoConstructor.Invoke(constructorArguments.ToArray());
        }
    }
}